// Merges proprietary price data from S&P Global into GHGRP by hub at the sub-basin level, then calculates facility average
// Author Levi Marks levi.marks.1>at>gmail.com

set more off
global DATA "[Insert Path Here]"

use "$DATA/ghgrp_hub_crosswalk", clear

// Merge price data
merge m:1 hub year using "$DATA/Prices/prices", keepusing(p_spot)
drop if _merge == 2
drop _merge

bysort facility_id year: egen p_avg = mean(p_spot)
drop p_spot
rename p_avg p_spot

// Combine facilities that merged over the study period or through asset sales are under the same owner in the DI data (also used for merging other GHGRP variables with DI)
rename facility_id facility_id_2
gen facility_id = facility_id_2
order facility_id*, after(year)
replace facility_id = 1008702 if facility_id == 1008698 //Cimarex
replace facility_id = 1008544 if facility_id == 1008700 //Cimarex Permian
replace facility_id = 1008952 if facility_id == 1009143 //Exxon Mobil
replace facility_id = 1008523 if facility_id == 1008524 //Chesapeak
replace facility_id = 1008934 if facility_id == 1010621 //Castleton East Texas
replace facility_id = 1007487 if facility_id == 1012040 //Conocophillips to Tanos in East Texas 
replace facility_id = 1008128 if facility_id == 1008713 | facility_id == 1011936 | facility_id == 1012782 //EOG and Penn Virginia to Covey Park in East Texas
replace facility_id = 1009069 if facility_id == 1012728 //Quicksilver and Bluestone
replace facility_id = 1009687 if facility_id == 1012114 //Wildhorse
replace facility_id = 1012706 if facility_id == 1009052 //Hilcorp
replace facility_id = 1010397 if facility_id == 1008621 //Devon to Linn
replace facility_id = 1008914 if facility_id == 1011662 //Range Resoureces to EQT in Permian
replace facility_id = 1008296 if facility_id == 1008207 //Caerus and Encana in Permian
replace facility_id = 1011418 if facility_id == 1008165 //Linn Piceance
replace facility_id = 1009785 if facility_id == 1009094 //Oxy acquired by Laramie/Plains in Piceance
replace facility_id = 1008974 if facility_id == 1009076 //Devon to by BP in San Juan
replace facility_id = 1012724 if facility_id == 1008408 //Energen to Logos in San Juan
replace facility_id = 1005963 if facility_id == 1008094 //Samson to Red Willow in San Juan
replace facility_id = 1008697 if facility_id == 1008545 //Cimarex South Oklahoma
replace facility_id = 1010325 if facility_id == 1009793 //SM Energy and Oasis 
replace facility_id = 1009053 if facility_id == 1012612 //Chevron to Hilcorp
replace facility_id = 1009391 if facility_id == 1011803 //Apache Corporation

collapse (mean) p_spot, by(facility_id year)

order year facility_id p_spot
sort facility year
format p_spot %6.2fc

compress *
save "$DATA/Prices/ghgrp_prices", replace

